const connection = require('../../sql');

class VendorService {
  async createVendor({
    vName,
    remark,
    state,
    orgName,
    taxNum,
    bankName,
    bankCard,
    billInfo,
    email,
    address,
    phone,
    grade,
    create_id,
    updata_id
  }) {
    try {
      const now = new Date();
      const sql = `
        INSERT INTO basic_vendor (
          vName, remark, state, orgName, taxNum, bankName, bankCard,
          billInfo, email, address, phone, grade, create_id, updata_id,
          create_at, updata_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      const [result] = await connection.execute(sql, [
        vName || null,
        remark || null,
        state !== undefined ? state : null,
        orgName || null,
        taxNum || null,
        bankName || null,
        bankCard || null,
        billInfo || null,
        email || null,
        address || null,
        phone || null,
        grade !== undefined ? grade : null,
        create_id,
        updata_id,
        now,
        now
      ]);
      return { id: result.insertId };
    } catch (error) {
      throw error;
    }
  }

  async deleteVendors(ids) {
    try {
      const sql = `
        DELETE FROM basic_vendor
        WHERE id IN (${ids.map(() => '?').join(',')})
      `;
      const [result] = await connection.execute(sql, ids);
      if (result.affectedRows === 0) {
        throw new Error('没有可删除的供应商');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async updateVendor({
    id,
    vName,
    remark,
    state,
    orgName,
    taxNum,
    bankName,
    bankCard,
    billInfo,
    email,
    address,
    phone,
    grade,
    updata_id
  }) {
    try {
      const sql = `
        UPDATE basic_vendor
        SET
          vName = ?, remark = ?, state = ?, orgName = ?,
          taxNum = ?, bankName = ?, bankCard = ?,
          billInfo = ?, email = ?, address = ?,
          phone = ?, grade = ?, updata_id = ?, updata_at = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [
        vName || null,
        remark || null,
        state !== undefined ? state : null,
        orgName || null,
        taxNum || null,
        bankName || null,
        bankCard || null,
        billInfo || null,
        email || null,
        address || null,
        phone || null,
        grade !== undefined ? grade : null,
        updata_id,
        now,
        id
      ]);
      if (result.affectedRows === 0) {
        throw new Error('供应商不存在');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }

  async getVendorList({ page, limit, vName, state, phone, grade }) {
    const offset = (page - 1) * limit;
    let whereClause = '';
    const params = [];

    if (vName) {
      whereClause += ' AND v.vName LIKE ?';
      params.push(`%${vName}%`);
    }
    if (state !== undefined) {
      whereClause += ' AND v.state = ?';
      params.push(state);
    }
    if (phone) {
      whereClause += ' AND v.phone LIKE ?';
      params.push(`%${phone}%`);
    }
    if (grade !== undefined) {
      whereClause += ' AND v.grade = ?';
      params.push(grade);
    }

    const countSql = `
      SELECT COUNT(*) as count
      FROM basic_vendor v
      WHERE 1=1 ${whereClause}
    `;
    const listSql = `
      SELECT 
        v.id, v.vName, v.remark, v.state, v.orgName, v.taxNum,
        v.bankName, v.bankCard, v.billInfo, v.email, v.address,
        v.phone, v.grade, v.create_id, v.updata_id,
        v.create_at, v.updata_at,
        creator.username AS create_username,
        updater.username AS updata_username
      FROM basic_vendor v
      LEFT JOIN sys_user creator ON v.create_id = creator.id AND creator.del_flag = 'F'
      LEFT JOIN sys_user updater ON v.updata_id = updater.id AND updater.del_flag = 'F'
      WHERE 1=1 ${whereClause}
      ORDER BY v.create_at DESC
      LIMIT ? OFFSET ?
    `;

    try {
      const [countResult] = await connection.execute(countSql, params);
      const [rows] = await connection.execute(listSql, [...params, String(limit), String(offset)]);

      return {
        current: page,
        limit,
        totalPage: Math.ceil(countResult[0].count / limit) || 1,
        total: countResult[0].count,
        rows
      };
    } catch (error) {
      throw error;
    }
  }

  async updateVendorState({ id, newState, updata_id }) {
    try {
      const sql = `
        UPDATE basic_vendor
        SET state = ?, updata_id = ?, updata_at = ?
        WHERE id = ?
      `;
      const now = new Date();
      const [result] = await connection.execute(sql, [newState, updata_id, now, id]);
      if (result.affectedRows === 0) {
        throw new Error('没有可更新的供应商');
      }
      return { success: true };
    } catch (error) {
      throw error;
    }
  }
}

module.exports = new VendorService();
